package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.dao.Recommend;

public class SpikeRecommendService {

	public Page<Record> findByPage(int pageNum, int pageSize, String title, int gp, int state) {
		List<Object> params = new ArrayList<Object>();
		StringBuffer f = new StringBuffer();
		StringBuffer s = new StringBuffer();
		if (gp == 1) {
			f.append("SELECT R.id,S.sellerName,R.recommend,R.orderNo,R.state ");
			s.append("FROM t_spike_recommend R, t_bus_seller S WHERE R.gp=1 AND R.fkey=S.id ");
			if (StrKit.notBlank(title)) {
				s.append(" AND S.sellerName LIKE ?");
				params.add("%" + title + "%");
			}
			if (state == 1) {
				s.append(" AND R.recommend=1");
			} else if (state == 2) {
				s.append(" AND R.recommend=0");
			}
			s.append(" ORDER BY R.orderNo,R.id ASC ");
		}else if (gp == 10) {
			f.append("SELECT R.id,S.name,S.id as goodsid,R.recommend,R.orderNo,R.state,R.goodsType ");
			s.append("FROM t_spike_recommend R "
					+ "left join (select * from t_bus_goods where groupbuying=2 AND state=2) S on S.id = R.fkey AND R.goodsType = 1 "
					+ " WHERE  1=1 ");
			if (StrKit.notBlank(title)) {
				s.append(" AND S.name LIKE ? ");
				params.add("%" + title + "%");
			}
			if (gp!=0) {
				s.append(" AND R.gp = ? ");
				params.add(gp);
			}
			if (state == 1) {
				s.append(" AND R.recommend=1");
			}
			if (state == 2) {
				s.append(" AND R.recommend=0");
			}
			s.append(" ORDER BY R.orderNo,R.id ASC ");
		} else {
			f.append("select K.* ");
			s.append("from (SELECT R.id,S.name,S.id as goodsid,S.state as sState,G.name as egeName,G.id as egeId,G.state as gState,R.recommend,R.orderNo,R.state as rState,R.goodsType "
							+ "FROM t_spike_recommend R "
							+ "left join (select * from t_bus_ege_goods where state=2) G on G.id = R.fkey AND R.goodsType = 2 "
							+ "left join (select * from t_bus_goods where groupbuying=1 AND state=2) S on S.id = R.fkey AND R.goodsType = 1 "
							+ " WHERE  1=1 ");
			if (StrKit.notBlank(title)) {
				s.append(" AND S.name LIKE ? or G.name like ? ");
				params.add("%" + title + "%");
				params.add("%" + title + "%");
			}
			if (gp!=0) {
				s.append(" AND R.gp = ? ");
				params.add(gp);
			}
			if (state == 1) {
				s.append(" AND R.recommend=1");
			}
			if (state == 2) {
				s.append(" AND R.recommend=0");
			}
			s.append(" ORDER BY R.orderNo,R.id ASC ) K where gState=2 or sState=2");
		}
		

		return Db.paginate(pageNum, pageSize, f.toString(), s.toString(), params.toArray());
	}

	public boolean Delete(int id) {
		List<String> list = new ArrayList<String>();
		list.add(" DELETE FROM t_spike_recommend WHERE id="+id);
		Db.batch(list, list.size());
		return true;

	}

	public boolean addObject(String[] checkbox, String gp,String goodsType) {
		int batchSize = checkbox.length;
		List<String> sqlList = new ArrayList<String>();
		for (String fkey : checkbox) {
			sqlList.add("INSERT INTO t_spike_recommend (fkey,gp,goodsType) values(" + fkey + "," + gp + "," + goodsType + ")");
		}
		int[] result = Db.batch(sqlList, batchSize);

		return result.length > 0;
	}

	public boolean sortList(Map<String, String> sortMap) {
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for (String id : sortMap.keySet()) {
			sqlList.add("UPDATE t_spike_recommend SET orderNo=" + sortMap.get(id) + " WHERE id=" + id);
		}
		int[] result = Db.batch(sqlList, batchSize);
		return result.length > 0;

	}

	public Page<Record> findOutSideByPage(int pageNum, int pageSize, String title, int gp) {
		List<Object> params = new ArrayList<Object>();
		StringBuffer b = new StringBuffer();
		StringBuffer ss = new StringBuffer();
		if(gp==1){
			b.append("SELECT G.id,G.sellerName");
			ss.append("FROM t_bus_seller G WHERE G.id NOT IN(SELECT R.fkey FROM t_spike_recommend R WHERE R.gp=1)");
			if (StrKit.notBlank(title)) {
				ss.append(" AND G.sellerName LIKE ?");
				params.add("%" + title + "%");
			}
			
		}else if (gp==10) {
			b.append("SELECT G.id,G.name,G.goodsNum,G.sellPrice ");
			ss.append("FROM t_bus_goods G WHERE G.id NOT IN(SELECT R.fkey FROM t_spike_recommend R WHERE R.gp="+gp+")  AND G.groupbuying=2 AND G.state=2 ");
			if (StrKit.notBlank(title)) {
				ss.append(" AND G.name LIKE ?");
				params.add("%" + title + "%");
			}	
		}else{
			b.append("SELECT G.id,G.name,G.goodsNum,G.sellPrice ");
			ss.append("FROM t_bus_goods G WHERE G.id NOT IN(SELECT R.fkey FROM t_spike_recommend R WHERE R.gp="+gp+")  AND G.groupbuying=1 AND G.state=2 ");
			if (StrKit.notBlank(title)) {
				ss.append(" AND G.name LIKE ?");
				params.add("%" + title + "%");
			}	
		}
		ss.append(" ORDER BY G.id DESC");

		return Db.paginate(pageNum, pageSize, b.toString(), ss.toString(), params.toArray());
	}
	/*查询兑换商品，并返回*/
	public Page<Record> findOutSideByPageEge(int pageNum, int pageSize, String title, int gp) {
		List<Object> params = new ArrayList<Object>();
		StringBuffer b = new StringBuffer();
		StringBuffer ss = new StringBuffer();
		if(gp==1){
			b.append("SELECT G.id,G.sellerName");
			ss.append("FROM t_bus_seller G WHERE G.id NOT IN(SELECT R.fkey FROM t_spike_recommend R WHERE R.gp=1)");
			if (StrKit.notBlank(title)) {
				ss.append(" AND G.sellerName LIKE ?");
				params.add("%" + title + "%");
			}
			
		}else{
			b.append("SELECT G.id,G.name,G.sellPrice ");
/*			ss.append("FROM (select K.* from t_bus_ege_goods K left join t_bus_ege_goods_spec S on S.goodId = K.id) as G WHERE G.id NOT IN(SELECT R.fkey FROM t_spike_recommend R WHERE R.gp="+gp+")"); */
			ss.append("FROM t_bus_ege_goods G WHERE G.id NOT IN(SELECT R.fkey FROM t_spike_recommend R WHERE R.gp="+gp+") AND G.state=2");
			if (StrKit.notBlank(title)) {
				ss.append(" AND G.name LIKE ?");
				params.add("%" + title + "%");
			}	
		}
		ss.append(" ORDER BY G.id DESC");

		return Db.paginate(pageNum, pageSize, b.toString(), ss.toString(), params.toArray());
	}

	public Recommend findById(int id) {
		return Recommend.me().findFirst("SELECT * FROM t_spike_recommend WHERE id=" + id);
	}

	public boolean edit(Recommend object) {
		return object.update();
	}
}